<?php
function libsys_import_photo_new_reader(&$context) {
	if(empty($context['sandbox']['conn'])||empty($context['sandbox']['conn']['smartibus'])||empty($context['sandbox']['conn']['libsys'])) {
		// Use php-oci8 rather than pdo_oci, which is faster
		$smartibus_datasource=\Drupal::config('libsys.smartibus.datasource');
		$conn=oci_pconnect($smartibus_datasource->get('username'),$smartibus_datasource->get('password'),
			sprintf("%s:%s/%s", $smartibus_datasource->get('host'), $smartibus_datasource->get('port'), $smartibus_datasource->get('database')),'AL32UTF8'
		);
		$context['sandbox']['conn']['smartibus']=$conn;

		$libsys_database = \Drupal::config('libsys.datasource');
		$conn=oci_pconnect($libsys_database->get('username'), $libsys_database->get('password'),
			sprintf("%s:%s/%s", $libsys_database->get('host'), $libsys_database->get('port'), $libsys_database->get('database')), 'AL32UTF8'
		);
		$context['sandbox']['conn']['libsys']=$conn;
	}

	if(empty($context['sandbox']['max'])) {
		$sql='SELECT COUNT(*) FROM READER_CERT WHERE cert_flag=1 AND NOT EXISTS('.
                        'SELECT * FROM reader_portrait WHERE reader_portrait.p_cert_id=reader_cert.cert_id)';
		$stmt=oci_parse($context['sandbox']['conn']['libsys'], $sql);
		oci_execute($stmt, OCI_DEFAULT);
		$row=oci_fetch_row($stmt);
		$context['sandbox']['max']=$row[0];
		$context['sandbox']['current']=' ';
		$context['sandbox']['count']=0;
		oci_free_statement($stmt);
	}

	if(empty($context['sandbox']['stmt']) || empty($context['sandbox']['stmt']['libsys']) || empty($context['sandbox']['stmt']['smartibus']) || empty($context['sandbox']['stmt']['libsys_delete']) || empty($context['sandbox']['stmt']['libsys_insert'])) {
		$sql='SELECT * FROM ('.
			'SELECT REDR_CERT_ID, CERT_ID FROM READER_CERT WHERE cert_flag=1 AND NOT EXISTS('.
			'SELECT * FROM reader_portrait WHERE reader_portrait.p_cert_id=reader_cert.cert_id) '.
			'ORDER BY REDR_CERT_ID ASC) t '.
			'WHERE t.REDR_CERT_ID>=:current_row AND rownum<=2';
		$stmt=oci_parse($context['sandbox']['conn']['libsys'], $sql);
		$context['sandbox']['stmt']['libsys']=$stmt;

		$sql='SELECT smt_photo FROM mid_photo WHERE smt_salaryno=:cert_id';
		$stmt=oci_parse($context['sandbox']['conn']['smartibus'], $sql);
		$context['sandbox']['stmt']['smartibus']=$stmt;

		$sql='DELETE READER_PORTRAIT WHERE P_CERT_ID=:cert_id';
		$stmt=oci_parse($context['sandbox']['conn']['libsys'], $sql);
		$context['sandbox']['stmt']['libsys_delete']=$stmt;

                $sql='INSERT INTO READER_PORTRAIT(REARDER_PORTRAIT, P_CERT_ID) VALUES (:photo, :reader)';
                $stmt=oci_parse($context['sandbox']['conn']['libsys'], $sql);
                $context['sandbox']['stmt']['libsys_insert']=$stmt;

	}

	oci_bind_by_name($context['sandbox']['stmt']['libsys'], ':current_row', $context['sandbox']['current']);
	$r=oci_execute($context['sandbox']['stmt']['libsys']);
	$row=oci_fetch_assoc($context['sandbox']['stmt']['libsys']);
	$redr_cert_id=$row['REDR_CERT_ID'];
	$cert_id=$row['CERT_ID'];
	$row=oci_fetch_assoc($context['sandbox']['stmt']['libsys']);
	if(empty($row)) {
		$next_cert_id=FALSE;
	}
	else $next_cert_id=$row['REDR_CERT_ID'];

	oci_bind_by_name($context['sandbox']['stmt']['smartibus'], ':cert_id', $redr_cert_id);
	oci_execute($context['sandbox']['stmt']['smartibus']);
	$row=oci_fetch_array($context['sandbox']['stmt']['smartibus'], OCI_ASSOC+OCI_RETURN_LOBS);
	$context['sandbox']['count']++;
	if(!empty($row['SMT_PHOTO']))  {
		$photo=$row['SMT_PHOTO'];
	
		oci_bind_by_name($context['sandbox']['stmt']['libsys_delete'], ':cert_id', $cert_id, -1, SQLT_CHR);
		oci_bind_by_name($context['sandbox']['stmt']['libsys_insert'], ':reader', $cert_id, -1, SQLT_CHR);
		oci_bind_by_name($context['sandbox']['stmt']['libsys_insert'], ':photo', $photo, -1, SQLT_LBI);

		oci_execute($context['sandbox']['stmt']['libsys_delete']);
		$r=oci_execute($context['sandbox']['stmt']['libsys_insert']);
		oci_commit($context['sandbox']['conn']['libsys']);
		$context['message'] = sprintf('Updated %s, %d of %d left.', $redr_cert_id, $context['sandbox']['max']-$context['sandbox']['count'], $context['sandbox']['max']);
		$context['results'][]=$redr_cert_id;
	} else {
		$context['message'] = sprintf('%s skipped, %d of %d left.', $redr_cert_id, $context['sandbox']['max']-$context['sandbox']['count'], $context['sandbox']['max']);
		\Drupal::logger('libsys')->warning('Photo of @reader has not been updated due to lack of source.',
			['@reader'=>$redr_cert_id]
		);
	}
	
	if(!empty($next_cert_id)) {
		$context['sandbox']['current']=$next_cert_id;
		$context['finished'] = $context['sandbox']['count'] / $context['sandbox']['max'];
	} else {
		$context['finished'] = 1;
		foreach($context['sandbox']['stmt'] as $s)
			oci_free_statement($s);
		foreach($context['sandbox']['conn'] as $c)
			oci_close($c);
	}
}

function libsys_import_photo_finished($success, $results, $operations) {
	if(count($results)==0)
		\Drupal::messenger()->addWarning(t('None of readers has been updated.', ['%num'=>count($results)]));
	else
		\Drupal::messenger()->addStatus(t('Photos of %num readers have been updated.', ['%num'=>count($results)]));
}

function libsys_sync_reader_binding($cert_id, $redr_cert_id, &$context) {
	\Drupal::moduleHandler()->alter('qyweixin_to_username', $redr_cert_id);

	try {
		$openid=\Drupal\qyweixin\CorpBase::userConvertToOpenid($redr_cert_id);
		if(empty($openid)) return;

		$libsys_database = \Drupal::config('libsys.datasource');
		$dbh = new PDO("oci:dbname=//".$libsys_database->get('host').'/'.$libsys_database->get('database').";charset=AL32UTF8",
			$libsys_database->get('username'), $libsys_database->get('password'));
		
		$sql='SELECT MAX(TO_NUMBER(NO)) FROM weixin_user';
		$no=$dbh->query($sql)->fetchColumn();
		$no++;

		$sql="INSERT INTO weixin_user(CERT_ID, WEIXIN_CODE, NO, USE_FLAG, IS_ADMIN, BIND_TIME) ".
			"VALUES(:certid, :weixincode, :no, '1', '0', sysdate)";

		$sth=$dbh->prepare($sql);
		$sth->execute([':certid'=>$cert_id, ':weixincode'=>$openid, ':no'=>$no]);

		$r=$sth->rowCount();
		if(!$r)
			\Drupal::messenger()->addError($sth->errorInfo());
		\Drupal::logger('libsys')->info(sprintf('%s: %s',$cert_id, $openid));
		$context['results'][]=$cert_id;
		$context['message'] = sprintf('Updated %s.', $redr_cert_id);
	} catch (\Exception $e) {
		\Drupal::logger('libsys')->error($e->getMessage());
		$context['sandbox']['failed'][]=$cert_id;
		$context['message'] = sprintf('Skipped %s.', $redr_cert_id);
	} finally {
		$dbh=null;
	} 
}

function libsys_sync_reader_binding_finished($success, $results, $operations) {
	if(count($results)==0)
		\Drupal::messenger()->addWarning(t('None of readers has been updated.', ['%num'=>count($results)]));
	else
		\Drupal::messenger()->addStatus(t('Bindings of %num readers have been updated.', ['%num'=>count($results)]));
}

function libsys_presto_to_list($url, &$context) {
	$cookieJar = \GuzzleHttp\Cookie\CookieJar::fromArray([
		'currency' => 'GBP'
	], 'prestomusic.com');
	$data = (string) \Drupal::httpClient()->get($url, ['cookies' => $cookieJar])->getBody();

	$doc = new \DOMDocument();
	$doc->loadHTML($data);
	$xpath = new \DOMXpath($doc);

	$elements = $xpath->query('//*[@id="a11y-content"]/div[1]/h1');
	$title='';
	$author='';
	foreach($elements as $element) {
		if($element->childNodes->length>1) {
			$element=$element->childNodes[2];
		}
		$title=trim($element->nodeValue);

		if(preg_match('/^[^:]+\:/', $title)) {
			preg_match_all('/^([^:]+)\: (.+)/', $title, $a);
			$title=trim($a[2][0]);
			$author=$a[1][0];
		}
	}

	if($author=='') {
		$elements = $xpath->query('//*[@id="a11y-content"]/div[1]/div[2]/ul/li');
		foreach($elements as $element) {
			if(empty($author))
				$author=$element->nodeValue;
		}
	}

	$elements = $xpath->query('//*[@id="about"]/div/ul/li');
	$isbn='';
	foreach($elements as $element) {
		if(empty($isbn) && preg_match('/ISBN: ([0-9X]{13}) (.+)/', $element->nodeValue)) {
			$isbn=preg_replace('/ISBN: ([0-9X]{13}) (.+)/', '${1}', $element->nodeValue);
		}
		if(empty($isbn) && preg_match('/ISMN: ([0-9]{13})(.+)/', $element->nodeValue)) {
			$isbn=preg_replace('/ISMN: ([0-9]{13})(.+)/', '${1}', $element->nodeValue);
		}
		if(empty($isbn) && preg_match('/EAN: ([0-9X]{13})/', $element->nodeValue)) {
			$isbn=preg_replace('/EAN: ([0-9X]{13})/', '${1}', $element->nodeValue);
		}
	}

	$elements = $xpath->query('//*[@id="a11y-content"]/div[2]/ul/li');
	$publisher=''; $pubyear='';
	foreach($elements as $element) {
		if(empty($publisher) && preg_match("/Publisher: /", $element->nodeValue)) {
			$publisher=preg_replace('/Publisher: (.+)/', '${1}',$element->nodeValue);
		}
		if(empty($pubyear) && preg_match("/Date Published: /", $element->nodeValue)) {
			$pubyear=preg_replace('/Date Published: ([\da-zA-Z ]+) (\d{4})/', '${2}',$element->nodeValue);
		}
		if(preg_match("/Edition Type: /", $element->nodeValue)) {
			$title.=' ('.preg_replace('/Edition Type: ([\da-zA-Z ]+)/', '${1}',$element->nodeValue).')';
		}
		if(preg_match("/Arrangement: /", $element->nodeValue)) {
			$title.=' ['.preg_replace('/Arrangement: ([\da-zA-Z\/ ]+)/', '${1}',$element->nodeValue).']';
		}
	}

	$elements = $xpath->query('//*[@id="main"]/div/div[1]/div[2]/div/div[1]/p');
	$price='';
	foreach($elements as $element) {
		preg_match_all('/([\d\.]+)/', $element->nodeValue, $a);
		if(empty($price)) $price=$a[0][0];
	}

	$context['message']=sprintf('Fetched %s (%s)', $title, $isbn);
	$context['results'][]=[
		$title, $author, $publisher, $isbn, $price, $pubyear
	];
	
}

function libsys_presto_to_list_finished($success, $results, $operations) {
	$filtered=0;
	$count=count($results);
	if($_SESSION['shouldfilter']) {
	$libsys_database = \Drupal::config('libsys.datasource');
	$dbh = new PDO("oci:dbname=//".$libsys_database->get('host').'/'.$libsys_database->get('database').";charset=AL32UTF8",
		$libsys_database->get('username'), $libsys_database->get('password'));

	$sql="SELECT 1 from dual WHERE EXISTS(SELECT * FROM MARC_IDX WHERE MARC_IDX_GRP='05' AND (MARC_IDX_CONT=:ISBN OR MARC_IDX_CONT=CONCAT('M',SUBSTR(:ISBN, 5,9))))";
	$sth=$dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));

	$orig_results=$results;
	$results=[];
	foreach($orig_results as $result) {
		$sth->execute([':ISBN'=>$result[3]]);
		$r=$sth->fetchAll();
		if(empty($r))
			$results[]=[$result[0], $result[1], $result[2], empty($result[3])?'':("'".$result[3]), $result[4], $result[5]];
		else $filtered++;
	}
	$dbh=null;
	}
	$_SESSION['booklist']=$results;
	\Drupal::messenger()->addStatus(sprintf('Fetched %d items.', count($results)));
	if($filtered)
		\Drupal::messenger()->addError(sprintf('%d items were filtered.', $filtered));
}
?>
